package br.com.egg.finances.dao;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Types;
import java.util.List;

import br.com.egg.finances.business.dao.util.BDUtils;
import br.com.egg.finances.business.exception.BusinessErrorException;
import br.com.egg.finances.model.Usuario;

public class UsuarioDAO extends DefaultDAO<Usuario> {

	@Override
	public void insert(Usuario entity) {
		Connection connection = null;
		CallableStatement callableStatement = null;
		try {
			StringBuilder sbCall = new StringBuilder();
			sbCall.append("{ CALL inserir_usuario(?, ?, ?, ?, ?, ?) }");
			
			String call = sbCall.toString();
			System.out.println(call);
			
			connection = BDUtils.getConnection();
			callableStatement = connection.prepareCall(call);
			callableStatement.setString(1, entity.getCdUsuario());
			callableStatement.setString(2, entity.getNmUsuario());
			callableStatement.setString(3, entity.getNmSenha());
			callableStatement.setString(4, entity.getCdUsuarioIndicador());
			callableStatement.setDouble(5, Double.valueOf(1));
			callableStatement.registerOutParameter(6, Types.VARCHAR);
			
			callableStatement.execute();
			
			validaChamdaProcedure(callableStatement, 6);
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, callableStatement, null);
		}
	}

	@Override
	public void update(Usuario entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbUpdate = new StringBuilder();
			sbUpdate.append("UPDATE DEGGUSUARIO SET NMUSUARIO = ?, NMSENHA = ? WHERE CDUSUARIO = ?");
			
			String update = sbUpdate.toString();
			System.out.println(update);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(update);
			preparedStatement.setString(1, entity.getNmUsuario());
			preparedStatement.setString(2, entity.getNmSenha());
			preparedStatement.setString(3, entity.getCdUsuario());
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public void delete(Usuario entity) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		try {
			StringBuilder sbDelete = new StringBuilder();
			sbDelete.append("DELETE FROM DEGGUSUARIO WHERE CDUSUARIO = ?");
			
			String delete = sbDelete.toString();
			System.out.println(delete);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(delete);
			preparedStatement.setString(1, entity.getCdUsuario());
			preparedStatement.executeUpdate();
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, null);
		}
	}

	@Override
	public Usuario find(Usuario entityFilter) {
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		try {
			StringBuilder sbSelect = new StringBuilder();
			sbSelect.append("SELECT USUARIO.CDUSUARIO AS CODIGO, USUARIO.NMUSUARIO AS NOME, USUARIO.NMSENHA AS SENHA, USUARIO.CDUSUARIOINDICADOR AS INDICADOR, USUARIO.VLCREDITO AS CREDITO ");
			sbSelect.append("FROM DEGGUSUARIO USUARIO WHERE USUARIO.CDUSUARIO = ? ");
			
			String select = sbSelect.toString();
			System.out.println(select);
			
			connection = BDUtils.getConnection();
			preparedStatement = connection.prepareStatement(select);
			preparedStatement.setString(1, entityFilter.getCdUsuario());
			resultSet = preparedStatement.executeQuery();
			
			Usuario usuario = null;
			if (resultSet.next()) {
				usuario = new Usuario();
				usuario.setCdUsuario(resultSet.getString("CODIGO"));
				usuario.setNmUsuario(resultSet.getString("NOME"));
				usuario.setNmSenha(resultSet.getString("SENHA"));
				usuario.setCdUsuarioIndicador(resultSet.getString("INDICADOR"));
				usuario.setVlCredito(resultSet.getDouble("CREDITO"));
			}
			
			return usuario;
		} catch (Exception e) {
			throw new BusinessErrorException(e);
		} finally {
			BDUtils.closeResources(connection, preparedStatement, resultSet);
		}
	}

	@Override
	public List<Usuario> findAll(String cdUsuario) {
		// TODO Auto-generated method stub
		return null;
	}

}
